Chapter 12 — Joins and Combining Data
Code Reference File — Copy and paste as needed

========================================
12.1.1 Inner Join
========================================
merged_inner = sales.merge(product, on='Product Code', how='inner')
print(merged_inner.head())

========================================
12.1.2 Left Join
========================================
merged_left = sales.merge(countries, left_on='Retailer City', right_on='City', how='left')
print(merged_left.head())

========================================
12.1.3 Right Join
========================================
merged_right = sales.merge(countries, left_on='Retailer City', right_on='City', how='right')
print(merged_right.head())

========================================
12.1.4 Outer Join
========================================
merged_outer = sales.merge(product, on='Product Code', how='outer')
print(merged_outer.head())

========================================
12.1.5 Debugging with indicator
========================================
merged_check = sales.merge(product, on='Product Code', how='outer', indicator=True)
print(merged_check['_merge'].value_counts())

========================================
12.2.1 Star Schema
========================================
sales_model = sales.merge(product, on='Product Code', how='left')
sales_model = sales_model.merge(countries, left_on='Retailer City', right_on='City', how='left')
print(sales_model.head())

========================================
12.3.1 Vertical Concatenation
========================================
df1 = sales.iloc[:5]
df2 = sales.iloc[5:10]
vertical_concat = pd.concat([df1, df2], axis=0)
print(vertical_concat)

========================================
12.3.2 Horizontal Concatenation
========================================
df1 = sales[['Product Code', 'Sale Price']].iloc[:5]
df2 = sales[['Quantity Sold', 'Retailer City']].iloc[:5]
horizontal_concat = pd.concat([df1, df2], axis=1)
print(horizontal_concat)

========================================
12.4.1 Pivot Table
========================================
pivot = sales.pivot_table(index='Retailer City', columns='Retailer Type', values='Revenue', aggfunc='sum', fill_value=0)
print(pivot.head())

========================================
12.4.2 Melt
========================================
melted = pd.melt(pivot.reset_index(), id_vars='Retailer City', var_name='Retailer Type', value_name='Revenue')
print(melted.head())

========================================
12.7.1 Total Cost
========================================
sales_model['Total Cost'] = sales_model['Product Cost'] * sales_model['Quantity Sold']

========================================
12.7.2 Status
========================================
sales_model['Status'] = sales_model['Country'].apply(lambda x: 'Local' if x == 'Australia' else 'Global')

========================================
12.7.3 Profit
========================================
sales_model['Profit'] = sales_model['Revenue'] - sales_model['Total Cost']
